<?php
/*! \brief
 * This is extension class SqlGen for Sqlite
 * \author William Su
 */
require_once dirname(__FILE__) . '/SqlGen.abstract.php';
class WFSqlGenSqlite extends ASqlGen
{
    public function __construct($objFramework){
        $strPrefixSymbolTable = '[';
        $strSuffixSymbolTable = ']';
        parent::__construct($objFramework, $strPrefixSymbolTable, $strSuffixSymbolTable);
    }
    public function getCreateDatabase(){
        $ret = ''; //Sqlite no need to create database
        return $ret;
    }
    public function getCreateTableWidget(){
        $ret = 'CREATE TABLE [WIDGET]
(
    [widget_id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [widget_name] VARCHAR(255),
    [modname] VARCHAR(50),
    [widget_version] VARCHAR(255),
    [widget_version_float] FLOAT default 0,
    [widget_type] INTEGER(4) default 1,
    [widget_min_wf_version] VARCHAR(255),
    [widget_min_wf_version_float] FLOAT default 0,
    [widget_status] INTEGER(4) default 0,
    [widget_status_update] INTEGER(4) default 0,
    [user_generated] INTEGER(4) default 0,
    [icon] VARCHAR(255),
    [icon_preview] VARCHAR(255),
    [widget_key] VARCHAR(50),
    [param] TEXT,
    [createtime] INTEGER(11),
    [edittime] INTEGER(11)
)';
        $ret .= ';CREATE INDEX idx_widget_name ON WIDGET (widget_name)';
        $ret .= ';CREATE INDEX idx_widget_version_float ON WIDGET (widget_version_float)';
        $ret .= ';CREATE INDEX idx_widget_min_wf_version_float ON WIDGET (widget_min_wf_version_float)';
        return $ret;
    }
    public function getCreateTableWidgetCategory(){
        $ret = 'CREATE TABLE [WIDGET_CATEGORY]
(
    [category_id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [category_name] VARCHAR(255),
    [createtime] INTEGER(11),
    [edittime] INTEGER(11)
)';
        $ret .= ';CREATE INDEX idx_category_name ON WIDGET_CATEGORY (category_name)';
        return $ret;
    }
    public function getCreateTableWidgetCategoryMapping(){
        $ret = 'CREATE TABLE [WIDGET_CATEGORY_MAPPING]
(
    [mapping_id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [widget_id] INTEGER(11)  NOT NULL,
    [category_id] INTEGER(11)  NOT NULL,
    [min_support_version] VARCHAR(255),
    [min_support_version_float] FLOAT default 0,
    [createtime] INTEGER(11),
    [edittime] INTEGER(11)
)';
        $ret .= ';CREATE INDEX idx_widget_id ON WIDGET_CATEGORY_MAPPING (widget_id)';
        $ret .= ';CREATE INDEX idx_category_id ON WIDGET_CATEGORY_MAPPING (category_id)';
        $ret .= ';CREATE INDEX idx_min_support_version_float ON WIDGET_CATEGORY_MAPPING (min_support_version_float)';
        return $ret;
    }
    public function getCreateTableWpUpdateFilter(){
        $ret = 'CREATE TABLE [WP_UPDATE_FILTER]
(
    [filter_id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [filter_key] VARCHAR(255),
    [file_interface] VARCHAR(255),
    [filter_target] VARCHAR(255),
    [filter_condition] VARCHAR(255),
    [createtime] INTEGER(11),
    [edittime] INTEGER(11)
)';
        $ret .= ';CREATE INDEX idx_filter_id ON WP_UPDATE_FILTER (filter_id)';
        return $ret;
    }
    public function getCreateTableWpUpdateLog(){
        $ret = '
CREATE TABLE [WP_UPDATE_LOG]
(
    [log_id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [status] INTEGER(4) default 0,
    [fail_point] INTEGER(6) default 0,
    [before_update_widgets] TEXT,
    [before_update_widget_components] TEXT,
    [createtime] INTEGER(11),
    [edittime] INTEGER(11)
)';
        return $ret;
    }
    public function getCreateTableProxylist(){
        $ret = 'CREATE TABLE [proxylist]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [owner] INTEGER(11),
    [deleted] INTEGER(11),
    [lastcheck] INTEGER(11),
    [name] TEXT,
    [hostinfo] TEXT
)';
        $ret .= ';CREATE INDEX idx_proxylist_owner ON proxylist (owner)';
        return $ret;
    }
    public function getCreateTableServerlist(){
        $ret = 'CREATE TABLE [serverlist]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [owner] INTEGER(11),
    [deleted] INTEGER(11),
    [lastcheck] INTEGER(11),
    [name] TEXT,
    [hostinfo] TEXT,
    [guid] VARCHAR(50)
)';
        $ret .= ';CREATE INDEX idx_serverlist_owner ON serverlist (owner)';
        return $ret;
    }
    public function getCreateTableServerlisttype(){
        $ret = 'CREATE TABLE [serverlisttype]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [type] VARCHAR(50) UNIQUE,
    [passwordonly] INTEGER(11),
    [customFields] TEXT,
    UNIQUE ([type])
)';
        return $ret;
    }
    public function getCreateTableUserdata(){
        $ret = 'CREATE TABLE [userdata]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [uid] INTEGER(11),
    [wid] INTEGER(11),
    [modname] VARCHAR(50),
    [data] TEXT,
    [pid] INTEGER(11),
    [widget_key] VARCHAR(50),
    [param] TEXT
)';
        $ret .= ';CREATE INDEX idx_uid ON userdata (uid)';
        $ret .= ';CREATE INDEX idx_wid ON userdata (wid)';
        $ret .= ';CREATE INDEX idx_pid ON userdata (pid)';
        return $ret;
    }
    public function getCreateTableUsers(){
        $ret = 'CREATE TABLE [users]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [email] VARCHAR(100),
    [pass] VARCHAR(50),
    [data] TEXT,
    [cdata] TEXT,
    [udata] TEXT,
    [pdata] TEXT,
    [lang] VARCHAR(10),
    [theme] VARCHAR(20)
)';
        return $ret;
    }
    public function getCreateTableContainer(){
        $ret = 'CREATE TABLE [CONTAINER]
(
    [container_id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [container_name] VARCHAR(255),
    [container_param] TEXT,
    [container_type_name] VARCHAR(255)
)';
        return $ret;
    }
    public function getCreateTableWidgetComponent(){
        $ret = 'CREATE TABLE [WIDGET_COMPONENT]
(
    [wcomponent_id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [wcomponent_name] VARCHAR(255),
    [wcomponent_version] VARCHAR(50),
    [wcomponent_version_float] FLOAT default 0,
    [wcomponent_min_support_version] VARCHAR(50),
    [wcomponent_min_support_version_float] FLOAT default 0,
    [wcomponent_status] INTEGER(4) default 0,
    [wcomponent_status_update] INTEGER(4) default 0,
    [wcomponent_param] TEXT,
    [createtime] INTEGER(11),
    [edittime] INTEGER(11)
)';
        $ret .= ';CREATE INDEX idx_wcomponent_name ON WIDGET_COMPONENT (wcomponent_name)';
        $ret .= ';CREATE INDEX idx_wcomponent_version_float ON WIDGET_COMPONENT (wcomponent_version_float)';
        $ret .= ';CREATE INDEX idx_wcomponent_min_wf_version_float ON WIDGET_COMPONENT (wcomponent_min_support_version_float)';
        return $ret;
    }
    public function getCreateTableWidgetComponentCategory(){
        $ret = 'CREATE TABLE [WIDGET_COMPONENT_CATEGORY]
(
    [category_id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [category_name] VARCHAR(255),
    [category_product_id] FLOAT,
    [category_proxy] VARCHAR(50),
    [createtime] INTEGER(11),
    [edittime] INTEGER(11)
)';
        $ret .= ';CREATE INDEX idx_wcomponent_category_name ON WIDGET_COMPONENT_CATEGORY (category_name)';
        return $ret;
    }
    public function getCreateTableWidgetComponentCategoryMapping(){
        $ret = 'CREATE TABLE [WIDGET_COMPONENT_CATEGORY_MAPPING]
(
    [mapping_id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [wcomponent_id] INTEGER(11)  NOT NULL,
    [category_id] INTEGER(11)  NOT NULL,
    [min_support_version] VARCHAR(50),
    [min_support_version_float] FLOAT default 0,
    [createtime] INTEGER(11),
    [edittime] INTEGER(11)
)';
        $ret .= ';CREATE INDEX idx_wcomponent_id ON WIDGET_COMPONENT_CATEGORY_MAPPING (wcomponent_id)';
        $ret .= ';CREATE INDEX idx_wcomponent_category_id ON WIDGET_COMPONENT_CATEGORY_MAPPING (category_id)';
        $ret .= ';CREATE INDEX idx_wcomponent_min_support_version_float ON WIDGET_COMPONENT_CATEGORY_MAPPING (min_support_version_float)';
        return $ret;
    }
    /*public function getDropTableWidget(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'WIDGET'.$this->strSuffixSymbolTable;
        return $ret;
    }
    public function getDropTableWidgetCategory(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'WIDGET_CATEGORY'.$this->strSuffixSymbolTable;
        return $ret;
    }
    public function getDropTableWidgetCategoryMapping(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'WIDGET_CATEGORY_MAPPING'.$this->strSuffixSymbolTable;
        return $ret;
    }
    public function getDropTableWpUpdateFilter(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'WP_UPDATE_FILTER'.$this->strSuffixSymbolTable;
        return $ret;
    }
    public function getDropTableWpUpdateLog(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'WP_UPDATE_LOG'.$this->strSuffixSymbolTable;
        return $ret;
    }
    public function getDropTableProxylist(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'proxylist'.$this->strSuffixSymbolTable;
        return $ret;
    }
    public function getDropTableServerlist(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'serverlist'.$this->strSuffixSymbolTable;
        return $ret;
    }
    public function getDropTableServerlisttype(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'serverlisttype'.$this->strSuffixSymbolTable;
        return $ret;
    }
    public function getDropTableUserdata(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'userdata'.$this->strSuffixSymbolTable;
        return $ret;
    }
    public function getDropTableUsers(){
        $ret = 'DELETE FROM '.$this->strPrefixSymbolTable.'users'.$this->strSuffixSymbolTable;
        return $ret;
    }*/
    public function getTruncateTableWidget(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'WIDGET' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='WIDGET';";
        return $ret;
    }
    public function getTruncateTableWidgetCategory(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'WIDGET_CATEGORY' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='WIDGET_CATEGORY';";
        return $ret;
    }
    public function getTruncateTableWidgetCategoryMapping(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'WIDGET_CATEGORY_MAPPING' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='WIDGET_CATEGORY_MAPPING';";
        return $ret;
    }
    public function getTruncateTableWpUpdateFilter(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'WP_UPDATE_FILTER' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='WP_UPDATE_FILTER';";
        return $ret;
    }
    public function getTruncateTableWpUpdateLog(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'WP_UPDATE_LOG' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='WP_UPDATE_LOG';";
        return $ret;
    }
    public function getTruncateTableProxylist(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'proxylist' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='proxylist';";
        return $ret;
    }
    public function getTruncateTableServerlist(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'serverlist' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='serverlist';";
        return $ret;
    }
    public function getTruncateTableServerlisttype(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'serverlisttype' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='serverlisttype';";
        return $ret;
    }
    public function getTruncateTableUserdata(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'userdata' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='userdata';";
        return $ret;
    }
    public function getTruncateTableUsers(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'users' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='users';";
        return $ret;
    }
    public function getTruncateTableContainer(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'CONTAINER' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='CONTAINER';";
        return $ret;
    }
    public function getTruncateTableWidgetComponent(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'WIDGET_COMPONENT' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='WIDGET_COMPONENT';";
        return $ret;
    }
    public function getTruncateTableWidgetComponentCategory(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'WIDGET_COMPONENT_CATEGORY' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='WIDGET_COMPONENT_CATEGORY';";
        return $ret;
    }
    public function getTruncateTableWidgetComponentCategoryMapping(){
        $ret = 'DELETE FROM ' . $this->strPrefixSymbolTable . 'WIDGET_COMPONENT_CATEGORY_MAPPING' . $this->strSuffixSymbolTable;
        $ret .= ";DELETE FROM sqlite_sequence WHERE name='WIDGET_COMPONENT_CATEGORY_MAPPING';";
        return $ret;
    }
}
